﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

using XianShang.Common;
using XianShang.Entity;

namespace XianShang.Data.SqlServer
{
    public partial class DataProvider : IDataProvider
    {
        /// <summary>
        /// 在匯款主表中插入一條新數據并返回自增ID值
        /// </summary>
        /// <param name="rbInfo"></param>
        /// <returns></returns>
        public long AddNewRemitBank(RemitBankInfo rbInfo)
        {
            DbParameter[] getPar ={
                                    DbHelper.MakeInParam("@area",(DbType)SqlDbType.TinyInt,3,rbInfo.Area),
                                    DbHelper.MakeInParam("@contacttype",(DbType)SqlDbType.NVarChar,50,rbInfo.ContactType),
                                     DbHelper.MakeInParam("@amount",(DbType)SqlDbType.Money,int.MaxValue,rbInfo.Amount),
                                     DbHelper.MakeInParam("@accountnum",(DbType)SqlDbType.TinyInt,3,rbInfo.AccountNum),
                                     DbHelper.MakeInParam("@name",(DbType)SqlDbType.NVarChar,50,rbInfo.Name),
                                     DbHelper.MakeInParam("@tel",(DbType)SqlDbType.NVarChar,30,rbInfo.Tel),
                                     DbHelper.MakeInParam("@msn",(DbType)SqlDbType.NVarChar,100,rbInfo.Msn),
                                     DbHelper.MakeInParam("@bbsaccount",(DbType)SqlDbType.NVarChar,50,rbInfo.BbsAccount),
                                     DbHelper.MakeInParam("@content",(DbType)SqlDbType.NVarChar,500,rbInfo.Content),
                                     DbHelper.MakeInParam("@status",(DbType)SqlDbType.TinyInt,3,rbInfo.Status),
                                     DbHelper.MakeInParam("@banknum",(DbType)SqlDbType.NVarChar,30,rbInfo.BankNum),
                                     DbHelper.MakeInParam("@remitclass",(DbType)SqlDbType.NVarChar,50,rbInfo.RemitClass),
                                     DbHelper.MakeInParam("@ip",(DbType)SqlDbType.NVarChar,50,rbInfo.IP),
                                     DbHelper.MakeInParam("@remittime",(DbType)SqlDbType.DateTime,rbInfo.RemitTime.Length,rbInfo.RemitTime),
                                     DbHelper.MakeInParam("@time",(DbType)SqlDbType.DateTime,DateTime.Now.ToString().Length,DateTime.Now.ToString())
                                 };

            string commandText = "INSERT INTO [dbo].[XS_RemitBank]";
            commandText += " ([Area],[AccountNum],[Amount],[Name],[BankNum],[RemitClass],[Tel],[ContactType],[Msn],[BbsAccount],[Content],[Status],[IP],[RemitTime],[Time])";
            commandText += "VALUES(@area,@accountnum,@amount,@name,@banknum,@remitclass,@tel,@contacttype,@msn,@bbsaccount,@content,@status,@ip,@remittime,@time)";
            commandText += ";SELECT @@IDENTITY";

            return Convert.ToInt64(DbHelper.ExecuteScalar(CommandType.Text, commandText, getPar));
        }

        /// <summary>
        /// 新增一條數據到（匯款子表_帳號數量）CardAccount
        /// </summary>
        /// <param name="caInfo"></param>
        /// <returns></returns>
        public int AddNewRemitAccount(RemitAccountInfo raInfo)
        {
            DbParameter[] getPar ={
                                     DbHelper.MakeInParam("@remitbankid",(DbType)SqlDbType.Int,Int32.MaxValue,raInfo.RemitBankId),
                                     DbHelper.MakeInParam("@account",(DbType)SqlDbType.NVarChar,100,raInfo.Account),
                                     DbHelper.MakeInParam("@accounttypes",(DbType)SqlDbType.NVarChar,100,raInfo.AccountTypes),
                                     DbHelper.MakeInParam("@time",(DbType)SqlDbType.DateTime,DateTime.Now.ToString().Length,DateTime.Now)
                                 };
            string commandText = "INSERT INTO [dbo].[XS_RemitAccount] ([RemitBankId],[Account],[Type],[Time])VALUES(@remitbankid,@account,@accounttypes,@time)";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }

        /// <summary>
        /// 查詢匯款狀態信息
        /// </summary>
        /// <param name="name"></param>
        /// <param name="tel"></param>
        /// <param name="gameAccount"></param>
        /// <param name="bankNum"></param>
        /// <returns></returns>
        public DataSet GetRemitBankAndRemitAccountInfo(string name, string tel, string gameAccount, string bankNum)
        {
            #region 根據條件處理參數
            List<string> arrFiled = new List<string>();
            List<string> arrCts = new List<string>();
            List<DbParameter> arrPar = new List<DbParameter>();


            if (!Utils.StrIsEmpty(name))
            {
                arrPar.Add(DbHelper.MakeInParam("@name", (DbType)SqlDbType.NVarChar, 50, name));
                arrCts.Add("[dbo].[XS_RemitBank].[name]=@name");
            }
            if (!Utils.StrIsEmpty(tel))
            {
                arrPar.Add(DbHelper.MakeInParam("@tel", (DbType)SqlDbType.NVarChar, 50, tel));
                arrCts.Add("[dbo].[XS_RemitBank].[tel]=@tel");
            }            
            if (!Utils.StrIsEmpty(bankNum))
            {
                arrPar.Add(DbHelper.MakeInParam("@banknum", (DbType)SqlDbType.NVarChar, 50, bankNum));
                arrCts.Add("[dbo].[XS_RemitBank].[banknum]=@banknum");
            }
            if (!Utils.StrIsEmpty(gameAccount))
            {
                arrPar.Add(DbHelper.MakeInParam("@account", (DbType)SqlDbType.NVarChar, 100, gameAccount));
                arrCts.Add("([dbo].[XS_RemitAccount].[account] =@account)");
            }
            arrPar.Add(DbHelper.MakeInParam("@status", (DbType)SqlDbType.TinyInt, 3, 4));
            arrCts.Add("[dbo].[XS_RemitBank].[status]<>@status");

            string conditions = null;

            DbParameter[] getPar = new DbParameter[arrPar.Count];
            for (int iCount = 0; iCount < arrPar.Count; ++iCount)
            {
                if (iCount == 0)
                {
                    conditions = "WHERE " + arrCts[iCount];
                }
                else
                {
                    conditions += " AND " + arrCts[iCount];
                }
                getPar[iCount] = arrPar[iCount];
            }

            #endregion

            string commandText = "SELECT [dbo].[XS_RemitBank].*,[dbo].[XS_RemitAccount].[Account],[dbo].[XS_RemitAccount].[Type] ";
            commandText += "FROM  [dbo].[XS_RemitAccount] INNER JOIN [dbo].[XS_RemitBank] ON [dbo].[XS_RemitAccount].[RemitBankId] = [dbo].[XS_RemitBank].[ID]";
            commandText += string.Format(" {0} ORDER BY [dbo].[XS_RemitBank].[time] DESC", conditions);

            return DbHelper.ExecuteDataset(CommandType.Text, commandText, getPar);
        }

        /// <summary>
        /// 根據ID查找匯款信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable GetRemitInfoById(string id)
        {
            #region sql自定義函數
            this.CreateRemitFun();
            #endregion

            DbParameter getPar = DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, int.MaxValue, id);
            string commandText = "";
            commandText += "SELECT  DISTINCT ";
            commandText += "[dbo].[XS_RemitBank].[area],[dbo].[XS_RemitBank].[AccountNum], [dbo].[XS_RemitBank].[Amount], [dbo].[XS_RemitBank].[Name], ";
            commandText += "[dbo].[XS_RemitBank].[BankNum],[dbo].[XS_RemitBank].[ContactType],[dbo].[XS_RemitBank].[RemitClass], [dbo].[XS_RemitBank].[Tel],";
            commandText += "[dbo].[XS_RemitBank].[Msn],[dbo].[XS_RemitBank].[BbsAccount], [dbo].[XS_RemitBank].[Content],";
            commandText += "[dbo].[XS_RemitBank].[Message], [dbo].[XS_RemitBank].[Status], [dbo].[XS_RemitBank].[IP], ";
            commandText += "[dbo].[XS_RemitBank].[ProcessIP], [dbo].[XS_RemitBank].[RemitTime],[dbo].[XS_RemitBank].[Time],";
            commandText += "[dbo].[XS_RemitBank].[ProcessTime], [dbo].[XS_RemitBank].[ID],";
            commandText += "[dbo].AddStrAccountClms([dbo].[XS_RemitBANK].[id]) AS [Account],";
            commandText += "[dbo].AddStrTypeClms([dbo].[XS_REMITBANK].[id]) AS [TYPE]";
            commandText += "FROM  [dbo].[XS_RemitBank] INNER JOIN [dbo].[XS_RemitAccount] ON [dbo].[XS_RemitAccount].[RemitBankId] = [dbo].[XS_RemitBank].[ID] ";
            commandText += "WHERE [dbo].[XS_RemitBank].[id]=@id ";

            return DbHelper.ExecuteDataset(CommandType.Text, commandText, getPar).Tables[0];
        }
        /// <summary>
        /// 根據STATUS和實體參數進行查詢返回總行數
        /// </summary>
        /// <param name="fr"></param>
        /// <param name="SearchType">查找方式（1精確0模糊）</param>
        /// <returns></returns>
        public object GetRemitBankAndRemitAccountInfoByStatusCount(FullRemitInfo fr, int SearchType)
        {
            #region sql自定義函數
            this.CreateRemitFun();
            #endregion

            #region 根據條件處理參數
            List<string> arrCts = new List<string>();
            List<DbParameter> arrPar = new List<DbParameter>();

            if (!Utils.StrIsEmpty(fr.Name))
            {
                arrPar.Add(DbHelper.MakeInParam("@name", (DbType)SqlDbType.NVarChar, 50, fr.Name));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_RemitBank].[name]=@name" : "[dbo].[XS_RemitBank].[name] LIKE '%'+@name+'%'");
            }
            if (!Utils.StrIsEmpty(fr.Tel))
            {
                arrPar.Add(DbHelper.MakeInParam("@tel", (DbType)SqlDbType.NVarChar, 50, fr.Tel));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_RemitBank].[tel]=@tel" : "[dbo].[XS_RemitBank].[tel] LIKE '%'+@tel+'%'");
            }
            if (!Utils.StrIsEmpty(fr.GameAccount))
            {
                arrPar.Add(DbHelper.MakeInParam("@account", (DbType)SqlDbType.NVarChar, 100, fr.GameAccount));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_RemitAccount].[account]=@account" : "[dbo].[XS_RemitAccount].[account] LIKE '%'+@account+'%'");
            }
            if (!Utils.StrIsEmpty(fr.BbsAccount))
            {
                arrPar.Add(DbHelper.MakeInParam("@bbaaccount", (DbType)SqlDbType.NVarChar, 50, fr.BbsAccount));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_RemitBank].[bbsaccount]=@bbaaccount" : "[dbo].[XS_RemitBank].[bbsaccount] LIKE '%'+@bbaaccount+'%'");
            }
            if (!Utils.StrIsEmpty(fr.IP))
            {
                arrPar.Add(DbHelper.MakeInParam("@ip", (DbType)SqlDbType.NVarChar, 50, fr.IP));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_RemitBank].[ip]=@ip" : "[dbo].[XS_RemitBank].[ip] LIKE '%'+@ip+'%'");
            }
            if (!Utils.StrIsEmpty(fr.RemitTime))
            {
                arrPar.Add(DbHelper.MakeInParam("@remittime", (DbType)SqlDbType.NVarChar, 50, fr.RemitTime));
                arrCts.Add(SearchType == 1 ? "DateDiff(DAY,[dbo].[XS_RemitBank].[remittime],@remittime)=0" : "YEAR([dbo].[XS_RemitBank].[remittime])=year(@remittime) AND MONTH([dbo].[XS_RemitBank].[remittime])=MONTH(@remittime) AND DAY([dbo].[XS_RemitBank].[remittime])=DAY(@remittime)");
            }
            if (!Utils.StrIsEmpty(fr.Area))
            {
                arrPar.Add(DbHelper.MakeInParam("@area", (DbType)SqlDbType.TinyInt, 4, fr.Area));
                arrCts.Add("[dbo].[XS_RemitBank].[area]=@area");
            }
            if (fr.Status!=-1)
            {
                arrPar.Add(DbHelper.MakeInParam("@status", (DbType)SqlDbType.TinyInt, 4, fr.Status));
                arrCts.Add("[dbo].[XS_RemitBank].[status]=@status");
            }

            string conditions = null;
            DbParameter[] getPar = new DbParameter[arrPar.Count];
            for (int iCount = 0; iCount < arrCts.Count; ++iCount)
            {
                if (0 == iCount)
                {
                    conditions += " WHERE " + arrCts[iCount];
                }
                else
                {
                    conditions += " AND " + arrCts[iCount];
                }
                getPar[iCount] = arrPar[iCount];
            }

            #endregion

            string commandText ="SELECT Count([dbo].[XS_RemitBank].[id]) AS [Count] ";
            commandText += "FROM  [dbo].[XS_RemitBank] INNER JOIN [dbo].[XS_RemitAccount] ON [dbo].[XS_RemitAccount].[RemitBankId] = [dbo].[XS_RemitBank].[ID] ";
            commandText += string.Format("{0}", conditions);

            return DbHelper.ExecuteScalar(CommandType.Text, commandText, getPar);
        }

        /// <summary>
        /// 根據實體參數進行查詢返回總行數
        /// </summary>
        /// <param name="fr"></param>
        /// <param name="SearchType">查找方式（1精確0模糊）</param>
        /// <returns></returns>
        public object GetRemitBankAndRemitAccountInfoCount(FullRemitInfo fr, int SearchType)
        {
            #region sql自定義函數
            this.CreateRemitFun();
            #endregion

            #region 根據條件處理參數
            List<string> arrCts = new List<string>();
            List<DbParameter> arrPar = new List<DbParameter>();

            if (!Utils.StrIsEmpty(fr.Name))
            {
                arrPar.Add(DbHelper.MakeInParam("@name", (DbType)SqlDbType.NVarChar, 50, fr.Name));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_RemitBank].[name]=@name" : "[dbo].[XS_RemitBank].[name] LIKE '%'+@name+'%'");
            }
            if (!Utils.StrIsEmpty(fr.Tel))
            {
                arrPar.Add(DbHelper.MakeInParam("@tel", (DbType)SqlDbType.NVarChar, 50, fr.Tel));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_RemitBank].[tel]=@tel" : "[dbo].[XS_RemitBank].[tel] LIKE '%'+@tel+'%'");
            }
            if (!Utils.StrIsEmpty(fr.GameAccount))
            {
                arrPar.Add(DbHelper.MakeInParam("@account", (DbType)SqlDbType.NVarChar, 100, fr.GameAccount));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_RemitAccount].[account]=@account" : "[dbo].[XS_RemitAccount].[account] LIKE '%'+@account+'%'");
            }
            if (!Utils.StrIsEmpty(fr.BbsAccount))
            {
                arrPar.Add(DbHelper.MakeInParam("@bbaaccount", (DbType)SqlDbType.NVarChar, 50, fr.BbsAccount));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_RemitBank].[bbsaccount]=@bbaaccount" : "[dbo].[XS_RemitBank].[bbsaccount] LIKE '%'+@bbaaccount+'%'");
            }
            if (!Utils.StrIsEmpty(fr.IP))
            {
                arrPar.Add(DbHelper.MakeInParam("@ip", (DbType)SqlDbType.NVarChar, 50, fr.IP));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_RemitBank].[ip]=@ip" : "[dbo].[XS_RemitBank].[ip] LIKE '%'+@ip+'%'");
            }
            if (!Utils.StrIsEmpty(fr.RemitTime))
            {
                arrPar.Add(DbHelper.MakeInParam("@remittime", (DbType)SqlDbType.NVarChar, 50, fr.RemitTime));
                arrCts.Add(SearchType == 1 ? "DateDiff(DAY,[dbo].[XS_RemitBank].[remittime],@remittime)=0" : "YEAR([dbo].[XS_RemitBank].[remittime])=year(@remittime) AND MONTH([dbo].[XS_RemitBank].[remittime])=MONTH(@remittime) AND DAY([dbo].[XS_RemitBank].[remittime])=DAY(@remittime)");
            }
            if (!Utils.StrIsEmpty(fr.Area))
            {
                arrPar.Add(DbHelper.MakeInParam("@area", (DbType)SqlDbType.TinyInt, 4, fr.Area));
                arrCts.Add("[dbo].[XS_RemitBank].[area]=@area");
            }
            string conditions = null;

            DbParameter[] getPar = new DbParameter[arrPar.Count];
            for (int iCount = 0; iCount < arrCts.Count; ++iCount)
            {
                conditions += " AND " + arrCts[iCount];
                getPar[iCount] = arrPar[iCount];
            }

            #endregion

            string commandText = "";
            commandText += "SELECT  DISTINCT count([dbo].[XS_RemitBank].[id]) AS [Count] ";
            commandText += "FROM  [dbo].[XS_RemitBank] INNER JOIN [dbo].[XS_RemitAccount] ON [dbo].[XS_RemitAccount].[RemitBankId] = [dbo].[XS_RemitBank].[ID] ";
            commandText += "WHERE [dbo].[XS_RemitBank].[Status]<>4 ";
            commandText += string.Format("{0}", conditions);

            return DbHelper.ExecuteScalar(CommandType.Text, commandText, getPar);

        }

        /// <summary>
        /// 取得匯款類表
        /// </summary>
        /// <returns></returns>
        public IDataReader GetAllRemitInfo()
        {
            #region sql自定義函數
            this.CreateRemitFun();
            #endregion
            
            string commandText = "";
            commandText+="SELECT  DISTINCT ";
            commandText += "[dbo].[XS_RemitBank].[area],[dbo].[XS_RemitBank].[AccountNum], [dbo].[XS_RemitBank].[Amount], [dbo].[XS_RemitBank].[Name], ";
            commandText += "[dbo].[XS_RemitBank].[BankNum],[dbo].[XS_RemitBank].[ContactType],[dbo].[XS_RemitBank].[RemitClass], [dbo].[XS_RemitBank].[Tel],";
            commandText+="[dbo].[XS_RemitBank].[Msn],[dbo].[XS_RemitBank].[BbsAccount], [dbo].[XS_RemitBank].[Content],";
            commandText+="[dbo].[XS_RemitBank].[Message], [dbo].[XS_RemitBank].[Status], [dbo].[XS_RemitBank].[IP], ";
            commandText+="[dbo].[XS_RemitBank].[ProcessIP], [dbo].[XS_RemitBank].[RemitTime],[dbo].[XS_RemitBank].[Time],";
            commandText+="[dbo].[XS_RemitBank].[ProcessTime], [dbo].[XS_RemitBank].[ID],";
            commandText+="[dbo].AddStrAccountClms([dbo].[XS_RemitBANK].[id]) AS [Account],";
            commandText+="[dbo].AddStrTypeClms([dbo].[XS_REMITBANK].[id]) AS [TYPE]";
            commandText+="FROM  [dbo].[XS_RemitBank] INNER JOIN [dbo].[XS_RemitAccount] ON [dbo].[XS_RemitAccount].[RemitBankId] = [dbo].[XS_RemitBank].[ID] ";
            commandText += "WHERE [dbo].[XS_RemitBank].[Status]<>4 AND [dbo].[xs_remitaccount].[Type] IS NOT NULL AND [dbo].[xs_remitaccount].[ACCOUNT] IS NOT NULL ";
            commandText += " ORDER BY [dbo].[XS_RemitBank].[Time] DESC";

            return DbHelper.ExecuteReader(CommandType.Text, commandText);
        }
        /// <summary>
        /// 取得匯款類表
        /// </summary>
        /// <returns></returns>
        public IDataReader GetAllRemitInfo(string area)
        {
            #region sql自定義函數
            this.CreateRemitFun();
            #endregion
            DbParameter getPar = DbHelper.MakeInParam("@area", (DbType)SqlDbType.TinyInt, 3, area);
            string commandText = "";
            commandText += "SELECT  DISTINCT [dbo].[XS_RemitBank].*,";
            commandText += "[dbo].AddStrAccountClms([dbo].[XS_RemitBANK].[id]) AS [Account],";
            commandText += "[dbo].AddStrTypeClms([dbo].[XS_REMITBANK].[id]) AS [TYPE]";
            commandText += "FROM  [dbo].[XS_RemitBank] INNER JOIN [dbo].[XS_RemitAccount] ON [dbo].[XS_RemitAccount].[RemitBankId] = [dbo].[XS_RemitBank].[ID] ";
            commandText += "WHERE [dbo].[XS_RemitBank].[Status]<>4 AND [dbo].[XS_RemitBank].[area]=@area  AND [dbo].[xs_remitaccount].[Type] IS NOT NULL AND [dbo].[xs_remitaccount].[ACCOUNT] IS NOT NULL ";
            commandText += " ORDER BY [dbo].[XS_RemitBank].[Time] DESC";

            return DbHelper.ExecuteReader(CommandType.Text, commandText,getPar);
        }
        /// <summary>
        /// 根據實體參數進行查詢
        /// </summary>
        /// <param name="fr"></param>
        /// <param name="SearchType">查找方式（1精確0模糊）</param>
        /// <returns></returns>
        public IDataReader GetRemitBankAndRemitAccountInfo(FullRemitInfo fr, int SearchType)
        {
            #region sql自定義函數
            this.CreateRemitFun();
            #endregion

            #region 根據條件處理參數
            List<string> arrCts = new List<string>();
            List<DbParameter> arrPar = new List<DbParameter>();

            if (!Utils.StrIsEmpty(fr.Name))
            {
                arrPar.Add(DbHelper.MakeInParam("@name", (DbType)SqlDbType.NVarChar, 50, fr.Name));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_RemitBank].[name]=@name" : "[dbo].[XS_RemitBank].[name] LIKE '%'+@name+'%'");
            }
            if (!Utils.StrIsEmpty(fr.Tel))
            {
                arrPar.Add(DbHelper.MakeInParam("@tel", (DbType)SqlDbType.NVarChar, 50, fr.Tel));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_RemitBank].[tel]=@tel" : "[dbo].[XS_RemitBank].[tel] LIKE '%'+@tel+'%'");
            }
            if (!Utils.StrIsEmpty(fr.GameAccount))
            {
                arrPar.Add(DbHelper.MakeInParam("@account", (DbType)SqlDbType.NVarChar, 100, fr.GameAccount));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_RemitAccount].[account]=@account" : "[dbo].[XS_RemitAccount].[account] LIKE '%'+@account+'%'");
            }
            if (!Utils.StrIsEmpty(fr.BbsAccount))
            {
                arrPar.Add(DbHelper.MakeInParam("@bbsaccount", (DbType)SqlDbType.NVarChar, 50, fr.BbsAccount));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_RemitBank].[bbsaccount]=@bbsaccount" : "[dbo].[XS_RemitBank].[bbsaccount] LIKE '%'+@bbsaccount+'%'");
            }
            if (!Utils.StrIsEmpty(fr.IP))
            {
                arrPar.Add(DbHelper.MakeInParam("@ip", (DbType)SqlDbType.NVarChar, 50, fr.IP));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_RemitBank].[ip]=@ip" : "[dbo].[XS_RemitBank].[ip] LIKE '%'+@ip+'%'");
            }
            if (!Utils.StrIsEmpty(fr.RemitTime))
            {
                arrPar.Add(DbHelper.MakeInParam("@remittime", (DbType)SqlDbType.NVarChar, 50, fr.RemitTime));
                arrCts.Add(SearchType == 1 ? "DateDiff(DAY,[dbo].[XS_RemitBank].[remittime],@remittime)=0" : "YEAR([dbo].[XS_RemitBank].[remittime])=year(@remittime) AND MONTH([dbo].[XS_RemitBank].[remittime])=MONTH(@remittime) AND DAY([dbo].[XS_RemitBank].[remittime])=DAY(@remittime)");
            }
            if (!Utils.StrIsEmpty(fr.Area))
            {
                arrPar.Add(DbHelper.MakeInParam("@area", (DbType)SqlDbType.TinyInt, 4, fr.Area));
                arrCts.Add("[dbo].[XS_RemitBank].[area]=@area");
            }
            string conditions = null;

            DbParameter[] getPar = new DbParameter[arrPar.Count];
            for (int iCount = 0; iCount < arrCts.Count; ++iCount)
            {
                conditions += " AND " + arrCts[iCount];
                getPar[iCount] = arrPar[iCount];
            }

            #endregion

            string commandText = "";
            commandText += "SELECT  DISTINCT [dbo].[XS_RemitBank].*,";
            commandText += "[dbo].AddStrAccountClms([dbo].[XS_RemitBANK].[id]) AS [Account],";
            commandText += "[dbo].AddStrTypeClms([dbo].[XS_REMITBANK].[id]) AS [TYPE]";
            commandText += "FROM  [dbo].[XS_RemitBank] INNER JOIN [dbo].[XS_RemitAccount] ON [dbo].[XS_RemitAccount].[RemitBankId] = [dbo].[XS_RemitBank].[ID] ";
            commandText += "WHERE [dbo].[XS_RemitBank].[Status]<>4 ";
            commandText += string.Format("{0}", conditions);
            commandText += " ORDER BY [dbo].[XS_RemitBank].[Time] DESC";

            return DbHelper.ExecuteReader(CommandType.Text, commandText, getPar);
        }

        /// <summary>
        /// 根據STATUS和實體參數進行查詢
        /// </summary>
        /// <param name="fr"></param>
        /// <param name="SearchType">查找方式（1精確0模糊）</param>
        /// <returns></returns>
        public IDataReader GetRemitBankAndRemitAccountInfoByStatus(FullRemitInfo fr, int SearchType)
        {
            #region sql自定義函數
            this.CreateRemitFun();
            #endregion

            #region 根據條件處理參數
            List<string> arrCts = new List<string>();
            List<DbParameter> arrPar = new List<DbParameter>();

            if (!Utils.StrIsEmpty(fr.Name))
            {
                arrPar.Add(DbHelper.MakeInParam("@name", (DbType)SqlDbType.NVarChar, 50, fr.Name));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_RemitBank].[name]=@name" : "[dbo].[XS_RemitBank].[name] LIKE '%'+@name+'%'");
            }
            if (!Utils.StrIsEmpty(fr.Tel))
            {
                arrPar.Add(DbHelper.MakeInParam("@tel", (DbType)SqlDbType.NVarChar, 50, fr.Tel));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_RemitBank].[tel]=@tel" : "[dbo].[XS_RemitBank].[tel] LIKE '%'+@tel+'%'");
            }
            if (!Utils.StrIsEmpty(fr.GameAccount))
            {
                arrPar.Add(DbHelper.MakeInParam("@account", (DbType)SqlDbType.NVarChar, 100, fr.GameAccount));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_RemitAccount].[account]=@account" : "[dbo].[XS_RemitAccount].[account] LIKE '%'+@account+'%'");
            }
            if (!Utils.StrIsEmpty(fr.BbsAccount))
            {
                arrPar.Add(DbHelper.MakeInParam("@bbaaccount", (DbType)SqlDbType.NVarChar, 50, fr.BbsAccount));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_RemitBank].[bbsaccount]=@bbaaccount" : "[dbo].[XS_RemitBank].[bbsaccount] LIKE '%'+@bbaaccount+'%'");
            }
            if (!Utils.StrIsEmpty(fr.IP))
            {
                arrPar.Add(DbHelper.MakeInParam("@ip", (DbType)SqlDbType.NVarChar, 50, fr.IP));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_RemitBank].[ip]=@ip" : "[dbo].[XS_RemitBank].[ip] LIKE '%'+@ip+'%'");
            }
            if (!Utils.StrIsEmpty(fr.RemitTime))
            {
                arrPar.Add(DbHelper.MakeInParam("@remittime", (DbType)SqlDbType.NVarChar, 50, fr.RemitTime));
                arrCts.Add(SearchType == 1 ? "DateDiff(DAY,[dbo].[XS_RemitBank].[remittime],@remittime)=0" : "YEAR([dbo].[XS_RemitBank].[remittime])=year(@remittime) AND MONTH([dbo].[XS_RemitBank].[remittime])=MONTH(@remittime) AND DAY([dbo].[XS_RemitBank].[remittime])=DAY(@remittime)");
            }
            if (!Utils.StrIsEmpty(fr.Area))
            {
                arrPar.Add(DbHelper.MakeInParam("@area", (DbType)SqlDbType.TinyInt, 4, fr.Area));
                arrCts.Add("[dbo].[XS_RemitBank].[area]=@area");
            }
            if (fr.Status!=-1)
            {
                arrPar.Add(DbHelper.MakeInParam("@status", (DbType)SqlDbType.TinyInt, 4, fr.Status));
                arrCts.Add("[dbo].[XS_RemitBank].[status]=@status");
            }

            string conditions = null;
            DbParameter[] getPar = new DbParameter[arrPar.Count];
            for (int iCount = 0; iCount < arrCts.Count; ++iCount)
            {
                if (0 == iCount)
                {
                    conditions += " WHERE " + arrCts[iCount];
                }
                else
                {
                    conditions += " AND " + arrCts[iCount];
                }
                getPar[iCount] = arrPar[iCount];
            }

            #endregion

            string commandText = "";
            commandText += "SELECT  DISTINCT [dbo].[XS_RemitBank].*,";
            commandText += "[dbo].AddStrAccountClms([dbo].[XS_RemitBANK].[id]) AS [Account],";
            commandText += "[dbo].AddStrTypeClms([dbo].[XS_REMITBANK].[id]) AS [TYPE]";
            commandText += "FROM  [dbo].[XS_RemitBank] INNER JOIN [dbo].[XS_RemitAccount] ON [dbo].[XS_RemitAccount].[RemitBankId] = [dbo].[XS_RemitBank].[ID] ";
            commandText += string.Format("{0}", conditions);
            commandText += " ORDER BY [dbo].[XS_RemitBank].[Time] DESC";

            return DbHelper.ExecuteReader(CommandType.Text, commandText, getPar);
        }

        /// <summary>
        /// 更新匯款開通狀態
        /// </summary>
        /// <param name="remitBankId"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public int UpRemitBankStatus(string remitBankId, int status)
        {
            DbParameter[] getPar = {
                                       DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, int.MaxValue, remitBankId),
                                       DbHelper.MakeInParam("@status",(DbType)SqlDbType.TinyInt,4,status)
                                   };
            string commandText = "UPDATE [dbo].[XS_RemitBank] SET [dbo].[XS_RemitBank].[Status] = @status WHERE [dbo].[XS_RemitBank].[ID]=@id";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據ID更新狀態和處理信息
        /// </summary>
        /// <param name="id"></param>
        /// <param name="status"></param>
        /// <param name="content"></param>
        /// <returns></returns>
        public int UpRemitBankStatusAndMessage(string id, string status, string content)
        {
            DbParameter[] getPar = {
                                       DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, int.MaxValue, id),
                                       DbHelper.MakeInParam("@status",(DbType)SqlDbType.TinyInt,4,status),
                                       DbHelper.MakeInParam("@message",(DbType)SqlDbType.NVarChar,300,content)

                                   };
            string commandText = "UPDATE [dbo].[XS_RemitBank] SET [dbo].[XS_RemitBank].[message]=@message, [dbo].[XS_RemitBank].[Status] = @status WHERE [dbo].[XS_RemitBank].[ID]=@id";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }

        /// <summary>
        /// 刪除匯款包括關聯字表記錄
        /// </summary>
        /// <param name="remitId"></param>
        /// <returns></returns>
        public int DelRemitBankAndRemitAccount(string remitId)
        {
            DbParameter getPar = DbHelper.MakeInParam("@remitid", (DbType)SqlDbType.Int, int.MaxValue, remitId);
            string commandText = "DELETE FROM [dbo].[XS_RemitBank] WHERE [dbo].[XS_RemitBank].[ID]=@remitid";
            int iRows=DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
            if (iRows > 0)
            {
                commandText = "DELETE FROM [dbo].[XS_RemitAccount] WHERE [dbo].[XS_RemitAccount].[remitbankid]=@remitid";
                iRows = DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
            }
            return iRows;
        }
        
        
        #region 私有函數
        private void CreateRemitFun()
        {
            string sqlFun = "";
            sqlFun += "IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[AddStrTypeClms]') AND xtype in (N'FN', N'IF', N'TF'))\r\n";
            sqlFun += "BEGIN\r\n";
            sqlFun += "execute dbo.sp_executesql @statement = N'\r\n";
            sqlFun += "Create FUNCTION [dbo].[AddStrTypeClms](@id nvarchar(10)) returns nvarchar(4000)\r\n";
            sqlFun += "AS\r\n";
            sqlFun += "BEGIN\r\n";
            sqlFun += "declare @str varchar(5000)\r\n";
            sqlFun += "select @str = isnull(@str + '','' , '''') +Type  from XS_REMITBANK left join XS_REMITACCOUNT on XS_REMITBANK.id=XS_REMITACCOUNT.RemitBankId where XS_REMITBANK.id = @id\r\n";
            sqlFun += "return @str\r\n";
            sqlFun += "END\r\n";
            sqlFun += "'\r\n";
            sqlFun += "END\r\n";
            sqlFun += "GO\r\n";

            sqlFun += "IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[AddStrAccountClms]') AND xtype in (N'FN', N'IF', N'TF'))\r\n";
            sqlFun += "BEGIN\r\n";
            sqlFun += "execute dbo.sp_executesql @statement = N'Create FUNCTION [dbo].[AddStrAccountClms](@id nvarchar(10)) returns nvarchar(4000)\r\n";
            sqlFun += "AS\r\n";
            sqlFun += "BEGIN\r\n";
            sqlFun += "declare @str varchar(5000)\r\n";
            sqlFun += "select @str = isnull(@str + '','' , '''') +Account  from XS_REMITBANK left join XS_REMITACCOUNT on XS_REMITBANK.id=XS_REMITACCOUNT.RemitBankId where XS_REMITBANK.id = @id\r\n";
            sqlFun += "return @str\r\n";
            sqlFun += "END\r\n";
            sqlFun += "'\r\n";
            sqlFun += "END\r\n";
            sqlFun += "GO\r\n";
            DbHelper.ExecuteCommandWithSplitter(sqlFun);
        }
    #endregion
    }
    
}
